import MySQLdb

server, user, password, database = "localhost", "root", "ProtossPass", "protossmaindb"

class DataBase:

    def __init__(self, server, user, password, database):
        self.server = server
        self.user = user
        self.password = password
        self.database = database

        self.db = MySQLdb.connect(self.server, self.user, self.password, self.database)
        self.cursor = self.db.cursor()
    
    def getRoutineNamesFromDB(self):
        self.cursor.execute("""SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA="%s";""" %database)
        routinedata = list(self.cursor.fetchall())
        
        routines = {}
        for row in routinedata:
            routines[row[0]] = row[1] #{name:type}

        self.cursor.execute("""SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA = "%s";""" %database)
        viewdata = list(self.cursor.fetchall())
     
        views = {}
        for row in viewdata:
            views[row[2]] = "VIEW" #{name:type}
        total = {}
        for key, value in list(routines.iteritems()) + list(views.iteritems()):
            total[key] = value
        
        return total #routines

    def getDefinitionForName(self, Name, Type):
        sql = "SHOW CREATE %s %s" %(Type, Name)
        
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        return data

    def returnRoutinesWithCode(self, ignore):
        routines = self.getRoutineNamesFromDB()
        routineDefs = {}

        for routinename, routinetype in routines.iteritems():
            if routinename not in ignore:
                if routinetype == 'VIEW':#if the object is a view, then the code is at column[1]
                    code = self.getDefinitionForName(routinename, routinetype)[0][1]
                    routineDefs[routinename] = code
                else: #if the object is a routine, then the code is at column[2]
                    code = self.getDefinitionForName(routinename, routinetype)[0][2]
                    routineDefs[routinename] = code
            else:
                print "ignoring %s ..."  %routinename
        return routineDefs

if __name__ == "__main__":
    d = DataBase(server, user, password, database)

    routinesAndViews = d.returnRoutinesWithCode([])
    for name, code in routinesAndViews.iteritems():
        print "%s : %s" %(name,code)
         
    
